topic "";
[ $$0,0#00000000000000000000000000000000:Default]
[H6;0 $$1,0#05600065144404261032431302351956:begin]
[i448;a25;kKO9;2 $$2,0#37138531426314131252341829483370:codeitem]
[l288;2 $$3,0#27521748481378242620020725143825:desc]
[0 $$4,0#96390100711032703541132217272105:end]
[{_}%EN-US 
[s1;%- &]
[s2;:MainWin`:`:LoadMainListFromDb`(`):%- [@(0.0.255) void]_[* LoadMainListFromDb]()&]
[s3; [1 // http://www.postgresql.org/docs/current/interactive/textsearch`-intro.html#TEX
TSEARCH`-MATCHING]&]
[s3; [1 // Per session: set default`_text`_search`_config `= `'pg`_catalog.english`';]&]
[s3; [1 // One time: Step 1) ALTER TABLE mydb.songs`_001 ADD COLUMN 
textsearchable`_index`_col tsvector;]&]
[s3; [1 // One time: (took 3.7 secs for 70K rows) Step 2) ]&]
[s3;1 &]
[s3; [1 //maintenance`_work`_mem (integer)]&]
[s3; [1 //Specifies the maximum amount of memory to be used by maintenance 
operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD 
FOREIGN KEY. It defaults to 16 megabytes (16MB). Since only one 
of these operations can be executed at a time by a database session, 
and an installation normally doesn`'t have many of them running 
concurrently, it`'s safe to set this value significantly larger 
than work`_mem. Larger settings might improve performance for 
vacuuming and for restoring database dumps.]&]
[s3;1 &]
[s3; [1 //UPDATE mydb.songs`_001 SET textsearchable`_index`_col `= 
to`_tsvector(`'english`', ]&]
[s3; [1 //-|coalesce(title,`'`') `|`| `' `' `|`| ]&]
[s3; [1 //-|coalesce(artistname,`'`') `|`| `' `' `|`| ]&]
[s3; [1 //-|coalesce(albumname,`'`') `|`| `' `' `|`| ]&]
[s3; [1 //-|coalesce(filepath,`'`') `|`| `' `' `|`| ]&]
[s3; [1 //-|coalesce(composername,`'`') `|`| `' `' `|`| ]&]
[s3; [1 //-|coalesce(lyrics,`'`'));]&]
[s3;1 &]
[s3; [1 -|-|-|-|-|// One time: (4.9 s) Step 3) ]&]
[s3; [1 -|-|-|-|-|// CREATE INDEX IX`_songs`_001`_textsearch ON mydb.songs`_001 
USING gin(textsearchable`_index`_col);]&]
[s3; [1 -|-|-|-|-|]&]
[s3; [1 -|-|-|-|-|// One time: Step 4) ]&]
[s3; [1 // CREATE TRIGGER songs`_001`_tsvectorupdate BEFORE INSERT 
OR UPDATE]&]
[s3; [1 // ON songs`_001 FOR EACH ROW EXECUTE PROCEDURE]&]
[s3; [1 // tsvector`_update`_trigger(textsearchable`_index`_col, `'pg`_catalog.english`'
, title, artistname, albumname, filepath, composername, lyrics);]&]
[s3; [1 -|-|-|-|-|]&]
[s3; [1 -|-|-|-|-|// TODO: Add column for parent directory or two.. but this 
could create problems for searches like `"Music`" :)  Need to 
make it smart.  If we know the base directory (E:`\`\Music`\`\), 
then we can just use things below that.]&]
[s3;1 &]
[s3; [1 // Example of weighting:]&]
[s3; [1 //-|-|-|-|-|-|UPDATE tt SET ti `=]&]
[s3; [1 //-|-|-|-|-|-|    setweight(to`_tsvector(coalesce(title,`'`')), `'A`') 
   `|`|]&]
[s3; [1 //-|-|-|-|-|-|    setweight(to`_tsvector(coalesce(keyword,`'`')), 
`'B`')  `|`|]&]
[s3; [1 //-|-|-|-|-|-|    setweight(to`_tsvector(coalesce(abstract,`'`')), 
`'C`') `|`|]&]
[s3; [1 //-|-|-|-|-|-|    setweight(to`_tsvector(coalesce(body,`'`')), `'D`');]&]
[s3;1 &]
[s3; [1 // Example of headlining and ranking:]&]
[s3; [1 //-|-|-|-|-|-|Edit this to use Qtf codes!-|-|-|-|-|]&]
[s3; [1 //-|-|-|-|-|-|SELECT id, ts`_headline(body, q, `'StartSel`=<b>, StopSel`=</b>, 
MaxWords`=35, MinWords`=15, ShortWord`=3, HighlightAll`=FALSE`'), 
rank]&]
[s3; [1 //-|-|-|-|-|-|FROM (SELECT id, body, q, ts`_rank`_cd(ti, q) AS rank]&]
[s3; [1 //-|-|-|-|-|-|      FROM apod, to`_tsquery(`'stars`') q]&]
[s3; [1 //-|-|-|-|-|-|      WHERE ti `@`@ q]&]
[s3; [1 //-|-|-|-|-|-|      ORDER BY rank DESC LIMIT 10) AS foo;]&]
[s4;%- &]
[s0; &]
[s0; -|-|-|-|-|-|// Add the EXCEPT (MINUS) SET operator to exclude yuckies&]
[s0; //-|-|-|-|-|-|cmd.Cat()&]
[s0; //-|-|-|-|-|-|<< `" EXCEPT `"&]
[s0; //-|-|-|-|-|-|<< `" SELECT `" &]
[s0; //-|-|-|-|-|-|<< SONG`_COLUMN`_LIST &]
[s0; //-|-|-|-|-|-|<< `" from songs`_001 `"&]
[s0; //-|-|-|-|-|-|<< `" WHERE textsearchable`_index`_col `"&]
[s0; //-|-|-|-|-|-|<< `" `@`@ to`_tsquery(`'english`', `'fuck `| penis 
`| explicit `| porno `| porn`') `";&]
[s0; &]
[s0; /`*&]
[s0;   Tests:&]
[s0;   SELECT `* FROM ts`_parse(`'default`', `'123 number Arcade.mp3`');&]
[s0;   SELECT `* FROM ts`_token`_type(`'default`');&]
[s0;   SELECT ts`_lexize(`'english`_stem`', `'stars`'); // `{star`}&]
[s0;   SELECT `* FROM ts`_debug(`'english`',`'a fat  cat sat on a 
mat `- it ate a fat rats`');&]
[s0;   SELECT title, ts`_rank`_cd(textsearch, query) AS rank&]
[s0;   FROM apod, to`_tsquery(`'neutrino`|(dark `& matter)`') query&]
[s0;   WHERE query `@`@ textsearch&]
[s0;   ORDER BY rank DESC LIMIT 10;&]
[s0;   SELECT ts`_headline(`'english`', `'The most common type of 
search&]
[s0;   is to find all documents containing given query terms &]
[s0;   and return them in order of their similarity to the&]
[s0;   query.`', to`_tsquery(`'query `& similarity`')); // Bolds 
searched text&]
[s0; `*/&]
[s0; &]
[s0; //cmd.Cat() << `" WHERE songname LIKE `'%`" << filter << `"%`' 
`";&]
[s0; -|-|-|-|-|-|//cmd.Cat() << `" WHERE songname `~`* `'`" << filter << 
`"`' `"; // The secret to Postgres case insensitive searching 
is to use regular expressions. If you`'re used to standard Unix/POSIX 
regular expressions, the implementation is pretty easy as well. 
Instead of using the standard database LIKE operator, you use 
the Postgres `~`* operator.&]
[s0; -|-|-|-|-|-|]